#!/usr/bin/env python
# -*- coding: utf-8 -*-


'''
Author: Bruno Alves 
Date: 2012/02/10
Version: v1.0
'''

import csv
import sqlite3
import time
from CSV import Csv

csv_location = '/home/admin1/estiglp1112-6152-python-rec-read-only/src/rebcsv/reb{0}.csv'

class Sql:

    
    def __init__(self):
        pass

    
    def newdb(self, db_file):	
        print 'Generating new database, wait a moment please...'
        timei = time.clock()
        ficheiro = file(db_file, 'w')
        ficheiro.close()
        conexao = sqlite3.connect(db_file)
        c = conexao.cursor()
        stmt = '''CREATE TABLE IF NOT EXISTS docentes (cod_docente INTEGER, nome TEXT, ultimo_grau TEXT, 
                curso TEXT, categoria TEXT, regime_tempo TEXT, ano_lectivo INTEGER, 
                tipo_estabelecimento TEXT, nome_estabelecimento TEXT)'''
        c.execute(stmt)
        conexao.commit()
        conexao.close()
        timef = time.clock()
        print 'Total Time  =', timef-timei
        print 'Finished'

      
    def loaddb(self, db_file):
        try:
            open(db_file, 'r')
            print 'Dumping CSVs to Database, wait a moment please...'
            timei = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            a = 0
            
            for ano in range(11):
                print '{0}º Year is being loaded into DataBase...'.format(ano)
                spamReader = csv.reader(open(csv_location.format(ano), 'rb'), delimiter=',')
                i = 0
                
                for row in spamReader:
                    stmt = '''INSERT INTO docentes VALUES("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
                           format(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8])
                    
                    if row[0] == 'Professor Code':
                        pass
                    else:
                        try:
                            
                            c.execute(stmt)
                        except:  
                            pass
                        pass
                    pass
                pass
            conexao.commit()
            conexao.close()
            timef = time.clock()
            print 'Total Time  =', timef-timei
            print 'Finished!!.'
            pass
        except:
            print 'Database not found'
            pass
   
      
    def teacherschoolyearfile(self, db_file):
        print 'Query in progress, wait a moment please...'
        try:         
            timei = time.clock()            
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT nome, tipo_estabelecimento, ano_lectivo FROM docentes
                    ORDER BY ano_lectivo, tipo_estabelecimento'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            timef = time.clock()
            print 'Total Time  =', timef-timei
            print 'Query Finished!!'
            
            file_name = 'StatsTeacherSchoolYear.csv'
            structure = 1

            
            obj = Csv()
            obj.DataFileSearch(file_name, data_list, structure)
            return file_name
        
            pass
        except:
            print 'DataBase Not Found!!!'
            pass

                  
    def searchteacherschoolyearcount(self, db_file):
        print 'Query in progress, wait a moment please...'
        try:
            timei = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT COUNT(nome), tipo_estabelecimento, ano_lectivo FROM docentes
                    GROUP BY tipo_estabelecimento, ano_lectivo
                    ORDER BY tipo_estabelecimento'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            timef = time.clock()
            print 'Total Time  = =', timef-timei
            print 'Query Finished!!'
        
            file_name = 'TeacherSchoolYearCount.csv'
            structure = 2

            
            obj = Csv()
            obj.DataFileSearch(file_name, data_list, structure)
            return file_name
        
            pass
        except:
            print 'DataBase Not Found!!!'
            pass    
 

        
    def TeacherCategorieSchoolYearFile(self, db_file):
        print 'Query in progress, wait a moment please...'
        try:
            timei = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT nome, categoria, tipo_estabelecimento, ano_lectivo FROM docentes
                    ORDER BY ano_lectivo, tipo_estabelecimento, categoria'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            timef = time.clock()
            print 'Total Time  =', timef-timei
            print 'Query Finished!!'
        
            file_name = 'TeacherbyCategorieSchoolYear.csv'
            structure = 3

            obj = Csv()
            obj.DataFileSearch(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'DataBase Not Found!!!'
            pass 
       
    def TeacherCategorieSchoolYearCount(self, db_file):
        print 'Query in progress, wait a moment please...'
        try:
            timei = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT COUNT(nome), categoria, tipo_estabelecimento, ano_lectivo FROM docentes
                    GROUP BY tipo_estabelecimento, categoria, ano_lectivo ORDER BY ano_lectivo'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            timef = time.clock()
            print 'Total Time = ', timef-timei
            print 'Query Finished!!'
  
            file_name = 'TeacherCategorieSchoolYearCount.csv'
            structure = 4

            data_list.pop() 

            obj = Csv()
            obj.DataFileSearch(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'DataBase Not Found!!!'
            pass

            
    def TeacherDegreeSchoolYearFile(self, db_file):
        print 'Query in progress, wait a moment please...'
        try:
            timei = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT nome, ultimo_grau, tipo_estabelecimento, categoria, ano_lectivo FROM docentes
                    ORDER BY ano_lectivo, tipo_estabelecimento, ultimo_grau, categoria'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            timef = time.clock()
            print 'Total Time = ', timef-timei
            print 'Query Finished'

            file_name = 'StatsTeacherDegreeSchoolYear.csv'
            structure = 5

            obj = Csv()
            obj.DataFileSearch(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'DataBase Not ok!!!'
            pass 

       
    def TeacherDegreeSchoolYearFileCount(self, db_file):
        print 'Query in progress, wait a moment please...'
        try:
            timei = time.clock()
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT COUNT(nome), categoria, ultimo_grau, tipo_estabelecimento, ano_lectivo FROM docentes
                    GROUP BY tipo_estabelecimento, ultimo_grau, categoria, ano_lectivo ORDER BY ano_lectivo'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            timef = time.clock()
            print 'Total Time = ' , timef-timei
            print 'Query in progress, wait a moment please...'

            file_name = 'StatsTeacherDegreeSchoolYearCount.csv'
            structure = 6

            data_list.pop() 

            
            obj = Csv()
            obj.DataFileSearch(file_name, data_list, structure)
            return file_name

            pass
        except:
            print 'DataBase Not Found!!!'
            pass
   
      
    def SchoolSearch(self, db_file):
        print 'Enable querys, please wait...'
        try:         
            conexao = sqlite3.connect(db_file)
            c = conexao.cursor()
            cmd = '''SELECT DISTINCT(nome_estabelecimento) FROM docentes
                    ORDER BY nome_estabelecimento'''
            c.execute(cmd)
            data_list = c.fetchall()
            conexao.commit()
            conexao.close()
            print 'OK'
            return data_list

            pass
        except:
            print 'DataBase Not Found!!!'
            pass


